FivetranのDestinationにSSHトンネルを介さないとアクセスできないMySQLを追加してみた
大阪オフィス所属だが現在は奈良県でリモートワーク中の玉井です。
Fivetranは色々なデータを1つのDBやDWHに集約できる便利なサービスですが、色々なデータを集約する以上、そのDBやDWHには堅牢なセキュリティが施されていることが多いと思います。（というか堅牢にしないといけない）。
今回は、踏み台サーバーを使用してSSHトンネルしないと接続できないDBを用意して、それをFivetranのDestinationとして設定する…っていうのを実際にやってみました。
注意事項
FivetranのDestinationにMySQLを設定することはできますが、推奨はされていません。多くのデータを連携するというサービスの性質を考えると、本番環境ではデータウェアハウス等を用意することが望ましいでしょう。
公式情報
検証の前提条件など
検証環境
下記の踏み台×MySQL（RDS）の環境を使います（このMySQLをDestination先に指定する）。これらの環境構築自体は既に終わっている前提です。
Fivetran側
初めてDestinationを設定する前提で行います。
やってみた
MySQLにFivetran用のユーザーを作成する
FivetranがMySQLにデータをロードするためのユーザーを用意します。
CREATE USER fivetran@'%' IDENTIFIED BY 'password'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW ON *.* TO fivetran@'%';
踏み台サーバーにFivetran用のユーザーを作成する
踏み台サーバーにSSHログインして、普通にユーザーを作成します。
fivetranというグループを作成します。
$ sudo groupadd fivetran
fivetranというユーザーを作成します。
$ sudo useradd -m -g fivetran fivetran
fivetranユーザーにスイッチします。
sudo su - fivetran
sshディレクトリを作成します。
mkdir ~/.ssh
作成したsshディレクトリのパーミッションを変更します。
chmod 700 ~/.ssh
作成したsshディレクトリに移動します。
cd ~/.ssh
公開鍵を作成します（後でFivetranから出力する公開鍵になります）。
touch authorized_keys
作成した公開鍵のパーミッションを変更します。
chmod 600 authorized_keys
Fivetranが踏み台サーバーへアクセスできるようにする（IPアドレスの許可）
踏み台サーバーに設定されているセキュリティグループを編集して、Fivetranが踏み台サーバーにアクセスできるようにします。
FivetranのIPアドレスは下記に記載されています。
上記を参考にしてIPアドレスをインバウンドグループに設定します（弊社のFivetranはUSリージョンなので、USリージョンのIPアドレスを全て登録します）。
Fivetranのユーザー権限がアカウントオーナーの場合、管理画面から疎通テストを行うことができます。
FivetranのDestinationにMySQLを追加する
Fivetranの画面左上からアカウント管理画面に行き、Destinationを新規追加します。
今回接続するMySQLはRDSなので「MySQL RDS」を選びます。
色々設定項目が出てきます。まず前半は、設定冒頭で作成したFivetran用のMySQLユーザーを入力します。
後半が今回の肝です。Connect via an SSH tunnelを選び、SSHトンネルするための踏み台サーバーの情報を入れていきます。
で、この画面でFivetranの公開鍵が払い出されるので、さっき踏み台サーバー上で作成した公開鍵ファイルにこいつを追記します。
一通り入力を終えて、接続テストを行います。
テスト通過！この時点で「FivetranからSSHトンネルをする」に成功しました。
念の為、実際にデータを連携してみる
Zendeskのサンドボックス環境のデータ（検証用のテキトーなデータしかない。個人情報一切ナシ。）を連携してみます。
連携は正常終了しました。
今度はMySQL側を確認。
mysql> show databases; +--------------------+ | Database | +--------------------+ | _fivetran_staging | | information_schema | | mysql | | performance_schema | | test | | zendesk | +--------------------+ 6 rows in set (0.00 sec)
mysql> show tables from zendesk; +----------------------+ | Tables_in_zendesk | +----------------------+ | alert_recipient | | brand | | custom_role | | daylight_time | | domain_name | | fivetran_audit | | forum_topic | | group | | group_member | | organization | | post | | schedule | | ticket | | ticket_alert | | ticket_comment | | ticket_custom_field | | ticket_field_history | | ticket_field_option | | ticket_form_history | | ticket_tag | | ticket_tag_history | | time_zone | | user | +----------------------+ 23 rows in set (0.00 sec)
mysql> show columns from zendesk.ticket; +-----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+-------+ | id | bigint(20) | NO | PRI | NULL | | | url | longtext | YES | | NULL | | | external_id | longtext | YES | | NULL | | | via_channel | longtext | YES | | NULL | | | via_source_from_id | bigint(20) | YES | | NULL | | | via_source_from_title | longtext | YES | | NULL | | | via_source_to_name | longtext | YES | | NULL | | | via_source_to_address | longtext | YES | | NULL | | | via_source_rel | longtext | YES | | NULL | | | merged_ticket_ids | json | YES | | NULL | | | created_at | timestamp(6) | YES | | NULL | | | updated_at | timestamp(6) | YES | | NULL | | | type | longtext | YES | | NULL | | | subject | longtext | YES | | NULL | | | description | longtext | YES | | NULL | | | priority | longtext | YES | | NULL | | | status | longtext | YES | | NULL | | | recipient | longtext | YES | | NULL | | | requester_id | bigint(20) | YES | | NULL | | | submitter_id | bigint(20) | YES | | NULL | | | assignee_id | bigint(20) | YES | | NULL | | | organization_id | bigint(20) | YES | | NULL | | | group_id | bigint(20) | YES | | NULL | | | forum_topic_id | bigint(20) | YES | | NULL | | | problem_id | bigint(20) | YES | | NULL | | | has_incidents | tinyint(1) | YES | | NULL | | | is_public | tinyint(1) | YES | | NULL | | | due_at | timestamp(6) | YES | | NULL | | | ticket_form_id | bigint(20) | YES | | NULL | | | brand_id | bigint(20) | YES | | NULL | | | allow_channelback | tinyint(1) | YES | | NULL | | | _fivetran_synced | timestamp(6) | YES | | NULL | | | system_location | longtext | YES | | NULL | | | system_client | longtext | YES | | NULL | | | system_latitude | double | YES | | NULL | | | system_longitude | double | YES | | NULL | | | system_ip_address | longtext | YES | | NULL | | +-----------------------+--------------+------+-----+---------+-------+ 37 rows in set (0.01 sec)
mysql> select id,status,subject,created_at from zendesk.ticket; +----+---------+-----------------------------------+----------------------------+ | id | status | subject | created_at | +----+---------+-----------------------------------+----------------------------+ | 1 | deleted | SCRUBBED | 2020-04-24 01:52:19.000000 | | 2 | pending | テスト | 2020-04-27 08:02:03.000000 | | 3 | open | テスト2 | 2020-04-27 08:22:35.000000 | | 4 | open | SUN | 2020-04-27 08:23:10.000000 | | 5 | open | よっつめ | 2020-04-27 08:24:42.000000 | | 6 | open | 5 | 2020-04-27 08:25:08.000000 | | 7 | deleted | チケット | 2020-06-04 00:20:37.000000 | | 8 | deleted | スケジュール外チケット | 2020-06-04 00:34:33.000000 | | 9 | deleted | タグ付けテスト10:26 | 2020-06-04 01:26:24.000000 | +----+---------+-----------------------------------+----------------------------+ 9 rows in set (0.01 sec)
ちゃんとデータが連携されていました（ひどいテストデータだな…）。
番外編 MySQLへの接続にハマる
本記事の内容と直接関係ないのですが、下記の件でハマったことを報告しておきます。
踏み台サーバーのMySQLクライアントをアップデートすることで解決。
おわりに
データウェアハウス等にSSHトンネル経由じゃないと入れないっていうのは結構あるあるなので、Fivetranはそういうときでも大丈夫ですよ！というお話でした。
